import random

from django.core.paginator import Paginator
from django.http import HttpResponse
from django.shortcuts import render

# Create your views here.
from App.models import User, Publisher, Book, Price


def index(request):
    # pub = Publisher(name='机械出版社')
    # pub.save()
    # book = Book(name='赤壁之战')
    # book.save()

    # book= Book.objects.get(pk=1)
    # pub = Publisher.objects.get(pk=1)
    # book.publisher = pub # 必须是对象 图书
    # book.save()

    # 添加
    # pub.books.create(name='红岩2')
    # pub.books.create(name='红岩3')
    # pub.books.add(book) # book 必须已经在数据库中
    # books = Book.objects.filter(pk__gt=3)
    # pub.books.bulk_create(list(books))

    # 删除
    # pub = Publisher.objects.get(pk=1)
    # pub.delete() # 级联删除

    # 更新
    # pub = Publisher.objects.get(pk=2)
    # pub.books.update(name='清华图书')

    # book = Book.objects.get(pk=5)
    # book.publisher.name = '清华Publisher'
    # book.publisher.save()

    # 查询
    # pub = Publisher.objects.get(pk=2)
    # print(pub.books.all()) # books是一个查询管理器
    # 复杂查询
    # pub = Publisher.objects.filter(books__name='清华图书') # 跨关系查
    # print(pub)

    # 一对一
    book = Book(name='赤壁之战2')
    book.save()
    price = Price(code='abc2', price=10)
    price.book = book
    price.save()  # 添加

    # book = Book.objects.get(pk=7)
    # book.delete() # 删除，同时删除price
    book = Book.objects.get(pk=8)
    print(book.price)

    p = Price.objects.get(pk=2)
    print(p.book)  # 跨关系查询

    return HttpResponse('index')


def handle_data(request):
    # 增加记录
    # user = User(username='tome', password='123456')
    # user = User(username='tome', password=md5(b'123456').hexdigest())
    # user.save()
    # user.id() # 显示id的值
    dict = {'username': 'tom*'}
    # create
    # User.objects.create(username='tome', password='123456') # 直接创建
    # User.objects.create(**dict)

    # 批量插入
    users = []
    for i in range(50):
        name = ''.join(random.sample('zyxwvutsrqponmlkjihgfedcba12345', 5))

        users.append(User.objects.create(username=str(name), password='123456'))
    # User.objects.bulk_create(
    #     User.objects.create(username='tom1', password='123456'),
    #     User.objects.create(username='tom2', password='123456')) # 直接创建
    User.objects.bulk_create(users)
    # 重复插入会报错

    # 修改
    # user = User.objects.get(pk=1)
    # user.password = '111111'
    # user.save()

    # 删除
    try:
        user = User.objects.get(id=1)  # Error User matching query does not exist. 不存在会错误
        if user:
            user.delete()
    except Exception as e:
        print('Error', e)
    # 删除多条

    users = User.objects.filter(id__gte=2)  # 获取大于=2的 返回QuerySet
    print(users)
    # users.delete()
    return HttpResponse('增删改查')


def query_data(request):
    data = User.objects.all()
    first = data.first()  # 取第一条
    part = data.filter(id__lt=50)  # 结果中过滤
    print(data)  # <QuerySet>

    # filter
    user = User.objects.filter(id=5)  # select * from user where id =5
    # users = User.objects.filter(id__gt = 2) # 获取大于2的 返回QuerySet
    user = User.objects.filter(id=5).filter(id__lt=50)  # >5 <50 过滤器 串联调用
    print(user)  # <QuerySet>

    User.objects.exclude(name='admin')
    User.objects.order_by('id')[:2]  # 结果集切片。 不能用负下标
    User.objects.order_by('-id')  # desc
    User.objects.order_by('password', '-id')

    User.objects.all().values()  # 所有字段的
    User.objects.values('name', 'password')  # 返回指定字段的数据

    # 去重 select disinct password from user
    User.objects.all().values('password').distinct()
    User.objects.order_by('id').reverse()  # 反
    User.objects.filter(id=5).exists()

    return HttpResponse('query_data')


def handle_filer(request):
    # 关系运算
    """
    >=  id__gte=2   id>=2
    >    id__gt=2   id>2
    <    id__lt
    <=   id__lte
    ==   id=2
    !=   id__ne
    """
    return HttpResponse('handle_filer')


def handle_aggregate(request):
    from django.db.models import Max, Min, Avg, Sum, Count
    id = User.objects.aggregate(Max('id'))
    print(id)  # {'id__max': 58}
    # 分组 select sex,count(id) from user group by sex
    data = User.objects.values('password').annotate(Count('id'))
    # data = User.objects.values('password').annotate(Count('id')).filter(sex=1)
    print(data)
    print(data.query)
    tmp = 'dic'
    user = User.objects.raw("select * from user where username='{}'".format(tmp))
    user = User.objects.raw("select * from user where username=%s", ["'sddsdds' or '1'"])  # 可防止sql注入攻击

    return HttpResponse('handle_aggregate')


def handle_manager(request):
    # User.user_manager.all() # 使用自定义manager
    data = User.password_manager.all()
    print(data)
    # 调用类方法
    data = User.after('2020-10-19')
    print(data)
    # 调用管理器的方法

    return HttpResponse('handle_manager')


def page(request, page=1):
    users = User.user_manager.all()
    paginator = Paginator(users, 10)
    pager = paginator.page(page)
    print(pager)
    print(pager.object_list)
    return HttpResponse('page')
